
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/22/2011 11:46:01
-- Generated from EDMX file: D:\Study\programming\DotNet\LearnByTest\CSharp\database\PlayEntityFramework\CourseManager\CourseManager\School.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [LearnEF];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Course_Department]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Courses] DROP CONSTRAINT [FK_Course_Department];
GO
IF OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OnlineCourses] DROP CONSTRAINT [FK_OnlineCourse_Course];
GO
IF OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OnsiteCourses] DROP CONSTRAINT [FK_OnsiteCourse_Course];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentGrade_Course]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StudentGrades] DROP CONSTRAINT [FK_StudentGrade_Course];
GO
IF OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OfficeAssignments] DROP CONSTRAINT [FK_OfficeAssignment_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentGrade_Student]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StudentGrades] DROP CONSTRAINT [FK_StudentGrade_Student];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CourseInstructor] DROP CONSTRAINT [FK_CourseInstructor_Course];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CourseInstructor] DROP CONSTRAINT [FK_CourseInstructor_Person];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Courses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Courses];
GO
IF OBJECT_ID(N'[dbo].[Departments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Departments];
GO
IF OBJECT_ID(N'[dbo].[OfficeAssignments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OfficeAssignments];
GO
IF OBJECT_ID(N'[dbo].[OnlineCourses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OnlineCourses];
GO
IF OBJECT_ID(N'[dbo].[OnsiteCourses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OnsiteCourses];
GO
IF OBJECT_ID(N'[dbo].[People]', 'U') IS NOT NULL
    DROP TABLE [dbo].[People];
GO
IF OBJECT_ID(N'[dbo].[StudentGrades]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StudentGrades];
GO
IF OBJECT_ID(N'[dbo].[CourseInstructor]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CourseInstructor];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Courses'
CREATE TABLE [dbo].[Courses] (
    [CourseID] int  NOT NULL,
    [Title] nvarchar(100)  NOT NULL,
    [Credits] int  NOT NULL,
    [DepartmentID] int  NOT NULL
);
GO

-- Creating table 'Departments'
CREATE TABLE [dbo].[Departments] (
    [DepartmentID] int  NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Budget] decimal(19,4)  NOT NULL,
    [StartDate] datetime  NOT NULL,
    [Administrator] int  NULL
);
GO

-- Creating table 'OfficeAssignments'
CREATE TABLE [dbo].[OfficeAssignments] (
    [InstructorID] int  NOT NULL,
    [Location] nvarchar(50)  NOT NULL,
    [Timestamp] binary(8)  NOT NULL
);
GO

-- Creating table 'OnlineCourses'
CREATE TABLE [dbo].[OnlineCourses] (
    [CourseID] int  NOT NULL,
    [URL] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'OnsiteCourses'
CREATE TABLE [dbo].[OnsiteCourses] (
    [CourseID] int  NOT NULL,
    [Location] nvarchar(50)  NOT NULL,
    [Days] nvarchar(50)  NOT NULL,
    [Time] datetime  NOT NULL
);
GO

-- Creating table 'People'
CREATE TABLE [dbo].[People] (
    [PersonID] int IDENTITY(1,1) NOT NULL,
    [LastName] nvarchar(50)  NOT NULL,
    [FirstName] nvarchar(50)  NOT NULL,
    [HireDate] datetime  NULL,
    [EnrollmentDate] datetime  NULL
);
GO

-- Creating table 'StudentGrades'
CREATE TABLE [dbo].[StudentGrades] (
    [EnrollmentID] int IDENTITY(1,1) NOT NULL,
    [CourseID] int  NOT NULL,
    [StudentID] int  NOT NULL,
    [Grade] decimal(3,2)  NULL
);
GO

-- Creating table 'CourseInstructor'
CREATE TABLE [dbo].[CourseInstructor] (
    [Courses_CourseID] int  NOT NULL,
    [People_PersonID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CourseID] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [PK_Courses]
    PRIMARY KEY CLUSTERED ([CourseID] ASC);
GO

-- Creating primary key on [DepartmentID] in table 'Departments'
ALTER TABLE [dbo].[Departments]
ADD CONSTRAINT [PK_Departments]
    PRIMARY KEY CLUSTERED ([DepartmentID] ASC);
GO

-- Creating primary key on [InstructorID] in table 'OfficeAssignments'
ALTER TABLE [dbo].[OfficeAssignments]
ADD CONSTRAINT [PK_OfficeAssignments]
    PRIMARY KEY CLUSTERED ([InstructorID] ASC);
GO

-- Creating primary key on [CourseID] in table 'OnlineCourses'
ALTER TABLE [dbo].[OnlineCourses]
ADD CONSTRAINT [PK_OnlineCourses]
    PRIMARY KEY CLUSTERED ([CourseID] ASC);
GO

-- Creating primary key on [CourseID] in table 'OnsiteCourses'
ALTER TABLE [dbo].[OnsiteCourses]
ADD CONSTRAINT [PK_OnsiteCourses]
    PRIMARY KEY CLUSTERED ([CourseID] ASC);
GO

-- Creating primary key on [PersonID] in table 'People'
ALTER TABLE [dbo].[People]
ADD CONSTRAINT [PK_People]
    PRIMARY KEY CLUSTERED ([PersonID] ASC);
GO

-- Creating primary key on [EnrollmentID] in table 'StudentGrades'
ALTER TABLE [dbo].[StudentGrades]
ADD CONSTRAINT [PK_StudentGrades]
    PRIMARY KEY CLUSTERED ([EnrollmentID] ASC);
GO

-- Creating primary key on [Courses_CourseID], [People_PersonID] in table 'CourseInstructor'
ALTER TABLE [dbo].[CourseInstructor]
ADD CONSTRAINT [PK_CourseInstructor]
    PRIMARY KEY NONCLUSTERED ([Courses_CourseID], [People_PersonID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [DepartmentID] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_Course_Department]
    FOREIGN KEY ([DepartmentID])
    REFERENCES [dbo].[Departments]
        ([DepartmentID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Course_Department'
CREATE INDEX [IX_FK_Course_Department]
ON [dbo].[Courses]
    ([DepartmentID]);
GO

-- Creating foreign key on [CourseID] in table 'OnlineCourses'
ALTER TABLE [dbo].[OnlineCourses]
ADD CONSTRAINT [FK_OnlineCourse_Course]
    FOREIGN KEY ([CourseID])
    REFERENCES [dbo].[Courses]
        ([CourseID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [CourseID] in table 'OnsiteCourses'
ALTER TABLE [dbo].[OnsiteCourses]
ADD CONSTRAINT [FK_OnsiteCourse_Course]
    FOREIGN KEY ([CourseID])
    REFERENCES [dbo].[Courses]
        ([CourseID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [CourseID] in table 'StudentGrades'
ALTER TABLE [dbo].[StudentGrades]
ADD CONSTRAINT [FK_StudentGrade_Course]
    FOREIGN KEY ([CourseID])
    REFERENCES [dbo].[Courses]
        ([CourseID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentGrade_Course'
CREATE INDEX [IX_FK_StudentGrade_Course]
ON [dbo].[StudentGrades]
    ([CourseID]);
GO

-- Creating foreign key on [InstructorID] in table 'OfficeAssignments'
ALTER TABLE [dbo].[OfficeAssignments]
ADD CONSTRAINT [FK_OfficeAssignment_Person]
    FOREIGN KEY ([InstructorID])
    REFERENCES [dbo].[People]
        ([PersonID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [StudentID] in table 'StudentGrades'
ALTER TABLE [dbo].[StudentGrades]
ADD CONSTRAINT [FK_StudentGrade_Student]
    FOREIGN KEY ([StudentID])
    REFERENCES [dbo].[People]
        ([PersonID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentGrade_Student'
CREATE INDEX [IX_FK_StudentGrade_Student]
ON [dbo].[StudentGrades]
    ([StudentID]);
GO

-- Creating foreign key on [Courses_CourseID] in table 'CourseInstructor'
ALTER TABLE [dbo].[CourseInstructor]
ADD CONSTRAINT [FK_CourseInstructor_Course]
    FOREIGN KEY ([Courses_CourseID])
    REFERENCES [dbo].[Courses]
        ([CourseID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [People_PersonID] in table 'CourseInstructor'
ALTER TABLE [dbo].[CourseInstructor]
ADD CONSTRAINT [FK_CourseInstructor_Person]
    FOREIGN KEY ([People_PersonID])
    REFERENCES [dbo].[People]
        ([PersonID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseInstructor_Person'
CREATE INDEX [IX_FK_CourseInstructor_Person]
ON [dbo].[CourseInstructor]
    ([People_PersonID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------